It’s not a good idea to hardcode the connection info in the pacakge, a better solution is store connection info in SSIS package configurations.
SSIS support different configuration type: XML/Environment variable/Registry entry/ Parent package variable/SQL server.
How to config it?
SSIS > Package configurations, New… chose the paramters you want to config.
then modify it in the configuration file or database.
Test: open your pacakge using Execute package utility, If you are going to use configuration file, you need to add the config file before executing, open the ‘Configurations’ tab, add your config file. if you are using SQL Server to store your configurations, no need to do this.
then, run your pacakge.
But SSIS can not encrypt data, there are 2 solutions:
i. using configuration file: NTFS encryption and/or ACLs to control access to config files and contents, or use third party tools.
ii. using SQL server: will store config info in database, use SQL security to control access.
1. SSIS Package Config File Encryption
2. encrypting ssis package configuration file
SQL Server: Export data to CVS with headers using BCP <->
// Proudly powered by Apache, PHP, MySQL, WordPress, Bootstrap, etc,.